﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Wicresoft.SelfService.DAL;
using Wicresoft.SelfService.Portal.Models;
using System.Data;
using Oracle.DataAccess.Client;

namespace Wicresoft.SelfService.Portal.Service
{
    public class UserAndRoleInfo
    {
        OracleDataAccess oracle = new OracleDataAccess();
        private const string sqlstep = "select n.AccountID,n.UserName,n.Company,n.DEPT,n.Position,n.Phone,m.RoleID,m.IsAdmin from accountcopy n inner join userrolerelationship m on n.ACCOUNTID=m.ACCOUNTID where m.roleid=";
        
        public List<UserRoleRelationshipModel> GetUserRoleRelationshipByRoleID(int RoleID,string company) 
        {
            string sql = sqlstep + RoleID + " and n.company='" + company + "'";
            DataTable ret = oracle.ExecuteFillToDataTable(sql);
            //var ret = OracleHelper.ExecuteDataTable(sql);
            List<UserRoleRelationshipModel> userList = new List<UserRoleRelationshipModel>();
            foreach (DataRow dr in ret.Rows)
            {
                UserRoleRelationshipModel user = new UserRoleRelationshipModel()
                {
                    AccountID = dr["AccountID"].ToString(),
                    Company = dr["Company"].ToString(),
                    DEPT = dr["DEPT"].ToString(),
                    IsAdmin = Convert.ToInt32(dr["IsAdmin"]),
                    Phone = dr["Phone"].ToString(),
                    Position = dr["Position"].ToString(),
                    RoleID = Convert.ToInt32(dr["RoleID"]),
                    UserName = dr["UserName"].ToString()
                };
                userList.Add(user);
            }
            return userList;
        }

        public string GetActionMessages(string requestid, string act) 
        {
            string sql = "select servicerequestid,accountid,action,datetime,comments,username from servicerecordshistory where servicerequestid= '" + requestid + "' and action= '" + act + "'";

            DataTable ret = oracle.ExecuteFillToDataTable(sql);
            string msg = "";
            foreach (DataRow dr in ret.Rows)
            {
                msg = dr["comments"].ToString();
                break;
            }
            return msg;
        }

        public string GetUserPhoneNumber(string accountid) 
        {
            string sql = "select accountid,username,company,dept,position,phone,email,disabled from accountcopy where disabled is null and accountid = '" + accountid + "'";
            DataTable ret = oracle.ExecuteFillToDataTable(sql);
            string phone = "";
            foreach (DataRow dr in ret.Rows)
            {
                phone = dr["phone"].ToString();
                break;
            }
            return phone;
        }

    }
}